-- investigate the transaction_log extended event
SELECT 
    oc.name, 
    oc.[type_name], 
    oc.[description]
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
    ON p.[guid] = o.package_guid
INNER JOIN sys.dm_xe_object_columns AS oc
    ON oc.[object_name] = o.name
		AND oc.object_package_guid = o.package_guid
WHERE o.name = N'transaction_log'
	AND oc.column_type = N'data';
GO

SELECT *
FROM sys.dm_xe_map_values
WHERE name = N'log_op'
	AND map_value = N'LOP_DELETE_SPLIT';
GO

-- extended event session to monitor page splits
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'PageSplits')
    DROP EVENT SESSION PageSplits ON SERVER;
GO

-- create the event session to track LOP_DELETE_SPLIT transaction_log operations
-- to find the worst splitting database_id
CREATE EVENT SESSION PageSplits ON SERVER
ADD EVENT sqlserver.transaction_log (
    WHERE operation = 11  -- LOP_DELETE_SPLIT
)
ADD TARGET package0.histogram (
    SET FILTERING_EVENT_NAME = 'sqlserver.transaction_log',
        SOURCE_TYPE = 0, -- Event Column
        SOURCE = 'database_id');
GO
        
-- start the event session
ALTER EVENT SESSION PageSplits ON SERVER STATE = START;
GO

-- monitor the workload

USE <database_name>;
GO

-- query the target data to identify the worst splitting database_id
SELECT 
    n.value('(value)[1]', 'bigint') AS database_id,
    DB_NAME(n.value('(value)[1]', 'bigint')) AS database_name,
    n.value('(@count)[1]', 'bigint') AS split_count
FROM
(SELECT CAST(target_data AS XML) target_data
 FROM sys.dm_xe_sessions AS s 
 INNER JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = 'PageSplits'
  AND t.target_name = 'histogram' ) AS tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') AS q(n);
 
/*
With the database_id of the worst splitting database,
we can then change our event session configuration to only look at this database,
and then change our histogram target configuration to bucket on the alloc_unit_id
so that we can then track down the worst splitting indexes in the database experiencing the worst mid-page splits.
*/

PRINT DB_ID('PageSplits');

-- drop the event session so we can recreate it to focus on the highest splitting database
DROP EVENT SESSION PageSplits ON SERVER;

-- create the event session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION PageSplits ON SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
      AND database_id = <database_id> -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'alloc_unit_id');
GO

-- start the event session again
ALTER EVENT SESSION PageSplits ON SERVER STATE = START;
GO

-- monitor the workload

USE <database_name>;  -- we have to be in the context of the database to get th results of the XE session
GO

-- query target data to get the top splitting objects in the database
SELECT
    o.name AS table_name,
    i.name AS index_name,
    tab.split_count,
    i.fill_factor
FROM (SELECT 
            n.value('(value)[1]', 'bigint') AS alloc_unit_id,
            n.value('(@count)[1]', 'bigint') AS split_count
        FROM
        (SELECT CAST(target_data AS XML) AS target_data
         FROM sys.dm_xe_sessions AS s 
         JOIN sys.dm_xe_session_targets t
             ON s.address = t.event_session_address
         WHERE s.name = 'PageSplits'
          AND t.target_name = 'histogram' ) AS tab
        CROSS APPLY target_data.nodes('HistogramTarget/Slot') AS q(n)
) AS tab
INNER JOIN sys.allocation_units AS au
    ON tab.alloc_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions AS p
    ON au.container_id = p.partition_id
INNER JOIN sys.indexes AS i
    ON p.object_id = i.object_id
        AND p.index_id = i.index_id
INNER JOIN sys.objects AS o
    ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0;
 
/*
With this information we can now go back and change our FillFactor specifications and retest/monitor the impact
to determine whether we’ve had the appropriate reduction in mid-page splits
to accommodate the time between our index rebuild operations
*/

-- change the fill factor based on split occurences
ALTER INDEX PK_MidSplits_RowID_MidSplitting ON MidSplits REBUILD
	WITH (FILLFACTOR = 70);
GO

-- stop the event session to clear the target
ALTER EVENT SESSION PageSplits ON SERVER STATE = STOP;
GO

-- start the event session again
ALTER EVENT SESSION PageSplits ON SERVER STATE = START;
GO

-- monitor the pagesplits again

-- stop the event session
ALTER EVENT SESSION PageSplits ON SERVER STATE = STOP;
GO
 
-- cleanup
DROP EVENT SESSION PageSplits ON SERVER;
GO